﻿Imports System.Data
Imports System.Data.Odbc
Imports System.Text
Imports System.Collections.Generic
Imports WSEntidades
Imports Utilerias
Imports WSFactory
Imports Npgsql
Imports MySql.Data.MySqlClient

Public Class CatServidoresDAL
#Region "FuncionesUtilizables"
    Private cadConex As String = ""

    Public Sub New()
        Me.cadConex = DBConnString.generarCadenaConexion(My.Settings.Server,
                                                         My.Settings.Port,
                                                         My.Settings.DB,
                                                         My.Settings.Un,
                                                         RijndaelSimple.desencriptaDefault(My.Settings.Up))
        'Me.cadConex = "Server=localhost;Port=5432;User Id=postgres;Password=123456;Database=test;Enlist=true"
        'Me.cadConex = "server=localhost;database=bdglobal;uid=root;password=MQ+123456;port=3306;"
    End Sub

    Public Function getServidorByEstatusyCantBD(ByVal pEstatus As CatServidoresBE.EstatusServidor) As CatServidoresBE
        Dim instrSql As String
        Dim tmpServidor As CatServidoresBE
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT Host, Descripcion, Username, Password, Port,IdServidor, Estatus, DisponiblesBD, CapacidadBD, DisponiblesBD " & _
                                            "FROM CatServidores " & _
                                            "WHERE Estatus = @estatus " & _
                                                "AND DisponiblesBD > 0;"
                    myComm.CommandType = CommandType.Text

                    Select Case pEstatus
                        Case CatServidoresBE.EstatusServidor.Activo
                            myComm.Parameters.AddWithValue("@estatus", "A")
                        Case CatServidoresBE.EstatusServidor.Inactivo
                            myComm.Parameters.AddWithValue("@estatus", "I")
                    End Select

                    myConn.Open()
                    Using myDR As MySqlDataReader = myComm.ExecuteReader()
                        If (myDR.HasRows()) Then
                            myDR.Read()
                            tmpServidor = CatServidoresFact.Build(myDR)
                        Else
                            tmpServidor = Nothing
                        End If
                    End Using
                End Using
            End Using
            Return tmpServidor

        Catch ex As Exception
            Throw
        Finally
            tmpServidor = Nothing
            instrSql = ""
        End Try
    End Function

    Public Function actualizaDisponiblesBdPorIdServidorActivo(ByVal pIdServ As Integer) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "UPDATE CatServidores " & _
                                                    "SET DisponiblesBD= DisponiblesBD - 1 " & _
                                                    "WHERE IdServidor = @Id " & _
                                                            "AND Estatus = @Estatus " & _
                                                            "AND DisponiblesBD > 0;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@Id", pIdServ)
                    myComm.Parameters.AddWithValue("@Estatus", "A")
                    myConn.Open()
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function
#End Region

    'Public Function registraCatServidores(ByVal pCatServidores As CatServidoresBE) As Boolean
    '    Dim instrSql As String
    '    Dim oInsParm(0 To 7) As OdbcParameter

    '    Try

    '        instrSql = "INSERT INTO ""CatServidores""(""Host"", ""NombreBd"", ""Usuario"", ""Pass"", ""Puerto"",""Id"", ""Status"", ""CantidadBD"") " & _
    '                                     "VALUES (?,?,?,?,?,?,?,?);"

    '        oInsParm(0) = New OdbcParameter("", pCatServidores.Host)
    '        oInsParm(1) = New OdbcParameter("", pCatServidores.NombreBd)
    '        oInsParm(2) = New OdbcParameter("", pCatServidores.Usuario)
    '        oInsParm(3) = New OdbcParameter("", pCatServidores.Pass)
    '        oInsParm(4) = New OdbcParameter("", pCatServidores.Puerto)
    '        oInsParm(5) = New OdbcParameter("", pCatServidores.Id)
    '        oInsParm(6) = New OdbcParameter("", pCatServidores.Status)
    '        oInsParm(7) = New OdbcParameter("", pCatServidores.CantidadBD)

    '        'Crea el objeto connection
    '        Using myConn As OdbcConnection = New OdbcConnection(My.Settings.cs)
    '            Using myComm As New OdbcCommand(instrSql, myConn)
    '                myComm.CommandType = CommandType.Text
    '                myComm.Parameters.AddRange(oInsParm)
    '                myConn.Open()
    '                myComm.ExecuteNonQuery()
    '                If (myComm.ExecuteNonQuery() > 0) Then
    '                    Return True
    '                Else
    '                    Return False
    '                End If
    '            End Using
    '        End Using
    '    Catch ex As Exception
    '        Throw
    '    Finally
    '        instrSql = Nothing
    '        oInsParm = Nothing
    '    End Try
    'End Function

    'Public Function modificaCatServidores(ByVal pCatServidores As CatServidoresBE) As Boolean
    '    Dim instrSql As String
    '    Dim oInsParm(0 To 7) As OdbcParameter
    '    Try
    '        instrSql = "UPDATE ""CatServidores"" " & _
    '                                    "SET ""Host""=?, ""NombreBd""=?, ""Usuario""=?, ""Pass""=?, ""Puerto""=?,""Status""=?, ""CantidadBD""=? " & _
    '                                            "WHERE ""Id"" = ?;"

    '        oInsParm(0) = New OdbcParameter("", pCatServidores.Host)
    '        oInsParm(1) = New OdbcParameter("", pCatServidores.NombreBd)
    '        oInsParm(2) = New OdbcParameter("", pCatServidores.Usuario)
    '        oInsParm(3) = New OdbcParameter("", pCatServidores.Pass)
    '        oInsParm(4) = New OdbcParameter("", pCatServidores.Puerto)
    '        oInsParm(5) = New OdbcParameter("", pCatServidores.Status)
    '        oInsParm(6) = New OdbcParameter("", pCatServidores.CantidadBD)
    '        oInsParm(7) = New OdbcParameter("", pCatServidores.Id)

    '        'Crea el objeto connection
    '        Using myConn As OdbcConnection = New OdbcConnection(My.Settings.cs)
    '            Using myComm As New OdbcCommand(instrSql, myConn)
    '                myComm.CommandType = CommandType.Text
    '                myComm.Parameters.AddRange(oInsParm)
    '                myConn.Open()
    '                myComm.ExecuteNonQuery()
    '                If (myComm.ExecuteNonQuery() > 0) Then
    '                    Return True
    '                Else
    '                    Return False
    '                End If
    '            End Using
    '        End Using

    '    Catch ex As Exception
    '        Throw
    '    Finally
    '        instrSql = Nothing
    '        oInsParm = Nothing
    '    End Try
    'End Function

    'Public Function getCatServidores(ByVal pId As Integer, ByVal pStrCatServidores As String) As List(Of CatServidoresBE)
    '    Dim CatServidoresLs As List(Of CatServidoresBE) = New List(Of CatServidoresBE)
    '    Dim instrSql As String

    '    Try
    '        instrSql = "SELECT ""Host"", ""NombreBd"", ""Usuario"", ""Pass"", ""Puerto"",""Id"", ""Status"", ""CantidadBD"" " & _
    '                                 "FROM ""CatServidores"" " & _
    '                                "WHERE ""Id"" = ? " & _
    '                                    "AND ""Status"" = '%" & pStrCatServidores & "%' ;"

    '        Using myConn As OdbcConnection = New OdbcConnection(My.Settings.cs)
    '            Using myComm As OdbcCommand = New OdbcCommand(instrSql, myConn)
    '                myComm.CommandType = CommandType.Text
    '                myComm.Parameters.Add(New OdbcParameter("", pId))
    '                myConn.Open()
    '                Using myDR As OdbcDataReader = myComm.ExecuteReader()
    '                    While myDR.Read()
    '                        Dim CatServidores As CatServidoresBE = CatServidoresFact.Build(myDR)
    '                        If (Not CatServidores Is Nothing) Then
    '                            CatServidoresLs.Add(CatServidores)
    '                        End If
    '                        CatServidores = Nothing
    '                    End While
    '                End Using
    '            End Using
    '        End Using
    '        Return CatServidoresLs
    '    Catch ex As Exception
    '        Throw
    '    Finally
    '        CatServidoresLs = Nothing
    '        instrSql = Nothing
    '    End Try
    'End Function

    Public Function existeServidor(ByVal pServidor As String, ByVal pId As Integer) As Boolean
        Dim existe As Boolean = False
        Dim instrSql As String
        Dim oSelParm(0 To 1) As OdbcParameter
        Try

            instrSql = "SELECT COUNT(*) " & _
                                    "FROM ""CatServidores"" " & _
                                    "WHERE ""NombreBd"" = ? " & _
                                        "AND ""Id"" = ?;"

            oSelParm(0) = New OdbcParameter("", pServidor)
            oSelParm(1) = New OdbcParameter("", pId)
            Dim count As Object = db.ExecScalar(instrSql, CommandType.Text, oSelParm, My.Settings.cs)
            If (Not count Is Nothing) Then
                If (CShort(count) > 0) Then
                    existe = True
                Else
                    existe = False
                End If
            Else
                existe = False
            End If
            Return existe

        Catch ex As Exception
            Throw
        Finally
            oSelParm = Nothing
            instrSql = Nothing
            existe = Nothing
        End Try
    End Function
End Class
